﻿
/* Configure SSIS Framework */

SET IDENTITY_INSERT [config].[Schedule] ON 

GO
INSERT [config].[Schedule] ([ScheduleID], [ScheduleName], [FrequencyType], [FrequencyInterval], [SubdayType], [SubdayInterval], [RelativeInterval], [StartTime], [EndTime]) VALUES (1, N'Hourly Schedule', N'D', 0, N'H', 1, NULL, 800, 1700)
GO
SET IDENTITY_INSERT [config].[Schedule] OFF
GO
SET IDENTITY_INSERT [config].[Application] ON 

GO
INSERT [config].[Application] ([ApplicationID], [ApplicationName], [RecoveryActionCode], [AllowParallelExecution], [ParallelChannels], [IsDisabled]) VALUES (1, N'Chinook BI ETL', N'R', 1, 4, 0)
GO
SET IDENTITY_INSERT [config].[Application] OFF
GO
SET IDENTITY_INSERT [config].[ApplicationSchedule] ON 

GO
INSERT [config].[ApplicationSchedule] ([ApplicationScheduleID], [ApplicationID], [ScheduleID], [LastRunDateTime], [NextRunDateTime], [IsEnabled], [IsDisabled]) VALUES (1, 1, 1, CAST(0x0000A1A40083D600 AS DateTime), CAST(0x0000A1A4009450C0 AS DateTime), 1, 0)
GO
SET IDENTITY_INSERT [config].[ApplicationSchedule] OFF
GO
SET IDENTITY_INSERT [config].[Package] ON 

GO
INSERT [config].[Package] ([PackageID], [PackagePath], [PackageName], [IsDisabled]) VALUES (1, N'ETLDimArtist.dtsx', N'ETLDimArtist', 0)
INSERT [config].[Package] ([PackageID], [PackagePath], [PackageName], [IsDisabled]) VALUES (2, N'ETLDimAlbum.dtsx', N'ETLDimAlbum', 0)
INSERT [config].[Package] ([PackageID], [PackagePath], [PackageName], [IsDisabled]) VALUES (3, N'ETLDimGerne.dtsx', N'ETLDimGerne', 0)
INSERT [config].[Package] ([PackageID], [PackagePath], [PackageName], [IsDisabled]) VALUES (4, N'ETLDimMediaType.dtsx', N'ETLDimMediaType', 0)
INSERT [config].[Package] ([PackageID], [PackagePath], [PackageName], [IsDisabled]) VALUES (5, N'ETLDimTrack.dtsx', N'ETLDimTrack', 0)
INSERT [config].[Package] ([PackageID], [PackagePath], [PackageName], [IsDisabled]) VALUES (6, N'ETLDimCustomer.dtsx', N'ETLDimCustomer', 0)
INSERT [config].[Package] ([PackageID], [PackagePath], [PackageName], [IsDisabled]) VALUES (7, N'ETLFactSale.dtsx', N'ETLFactSale', 0)
GO
SET IDENTITY_INSERT [config].[Package] OFF
GO
SET IDENTITY_INSERT [config].[Task] ON 

GO
INSERT [config].[Task] ([TaskID], [TaskName], [ApplicationID], [PackageID], [ParallelChannel], [ExecutionOrder], [PrecendentTaskID], [ExecuteAsync], [FailureActionCode], [RecoveryActionCode], [LastRunDateTime], [IsActive], [IsDisabled]) VALUES (1, N'ETLDimArtist', 1, 1, 1, 1, NULL, 0, N'A', N'R', CAST(0x0000A1A400BF80BC AS DateTime), 1, 0)
INSERT [config].[Task] ([TaskID], [TaskName], [ApplicationID], [PackageID], [ParallelChannel], [ExecutionOrder], [PrecendentTaskID], [ExecuteAsync], [FailureActionCode], [RecoveryActionCode], [LastRunDateTime], [IsActive], [IsDisabled]) VALUES (2, N'ETLDimAlbum', 1, 2, 1, 2, NULL, 0, N'A', N'R', CAST(0x0000A1A400BF80BC AS DateTime), 1, 0)
INSERT [config].[Task] ([TaskID], [TaskName], [ApplicationID], [PackageID], [ParallelChannel], [ExecutionOrder], [PrecendentTaskID], [ExecuteAsync], [FailureActionCode], [RecoveryActionCode], [LastRunDateTime], [IsActive], [IsDisabled]) VALUES (3, N'ETLDimGerne', 1, 3, 1, 3, NULL, 0, N'A', N'R', CAST(0x0000A1A400BF80BC AS DateTime), 1, 0)
INSERT [config].[Task] ([TaskID], [TaskName], [ApplicationID], [PackageID], [ParallelChannel], [ExecutionOrder], [PrecendentTaskID], [ExecuteAsync], [FailureActionCode], [RecoveryActionCode], [LastRunDateTime], [IsActive], [IsDisabled]) VALUES (4, N'ETLDimMediaType', 1, 4, 1, 4, NULL, 0, N'A', N'R', CAST(0x0000A1A400BF80BC AS DateTime), 1, 0)
INSERT [config].[Task] ([TaskID], [TaskName], [ApplicationID], [PackageID], [ParallelChannel], [ExecutionOrder], [PrecendentTaskID], [ExecuteAsync], [FailureActionCode], [RecoveryActionCode], [LastRunDateTime], [IsActive], [IsDisabled]) VALUES (5, N'ETLDimTrack', 1, 5, 2, 1, 4, 0, N'A', N'R', CAST(0x0000A1A400BF80BC AS DateTime), 1, 0)
INSERT [config].[Task] ([TaskID], [TaskName], [ApplicationID], [PackageID], [ParallelChannel], [ExecutionOrder], [PrecendentTaskID], [ExecuteAsync], [FailureActionCode], [RecoveryActionCode], [LastRunDateTime], [IsActive], [IsDisabled]) VALUES (6, N'ETLDimCustomer', 1, 6, 3, 1, 5, 0, N'A', N'R', CAST(0x0000A1A400BF80BC AS DateTime), 1, 0)
INSERT [config].[Task] ([TaskID], [TaskName], [ApplicationID], [PackageID], [ParallelChannel], [ExecutionOrder], [PrecendentTaskID], [ExecuteAsync], [FailureActionCode], [RecoveryActionCode], [LastRunDateTime], [IsActive], [IsDisabled]) VALUES (7, N'ETLFactSale', 1, 7, 3, 1, 6, 0, N'A', N'R', CAST(0x0000A1A400BF80BC AS DateTime), 1, 0)
GO
SET IDENTITY_INSERT [config].[Task] OFF
GO



/*Set Unknown values*/

SET IDENTITY_INSERT [dbo].[DimArtist] ON;
INSERT INTO [dbo].[DimArtist]
           ([ArtistId],[ArtistCode],[Name])
     VALUES
           (-1, -1, 'Unknown')
SET IDENTITY_INSERT [dbo].[DimArtist] OFF;

SET IDENTITY_INSERT [dbo].[DimAlbum] ON;
INSERT INTO [dbo].[DimAlbum]
           ([AlbumId],[AlbumCode],[Title],[ArtistId])
     VALUES
           (-1, -1, 'Unknown', -1)
SET IDENTITY_INSERT [dbo].[DimAlbum] OFF;

SET IDENTITY_INSERT [dbo].[DimGenre] ON;
INSERT INTO [dbo].[DimGenre]
           ([GenreId],[GenreCode],[Name])
     VALUES
           (-1, -1, 'Unknown')
SET IDENTITY_INSERT [dbo].[DimGenre] OFF;

SET IDENTITY_INSERT [dbo].[DimMediaType] ON;
INSERT INTO [dbo].[DimMediaType]
           ([MediaTypeId],[MediaTypeCode],[Name])
     VALUES
           (-1, -1, 'Unknown')
SET IDENTITY_INSERT [dbo].[DimMediaType] OFF;

SET IDENTITY_INSERT [dbo].[DimTrack] ON;
INSERT INTO [dbo].[DimTrack]
           ([TrackId], [TrackCode],[Name],[AlbumId],[MediaTypeId],[GenreId],[Composer],[Milliseconds],[UnitPrice], [SCDEffectiveFrom])
     VALUES
           (-1, -1, 'Unknown', -1, -1, -1, 'Unknown', 0, 0, '1900-01-01')
SET IDENTITY_INSERT [dbo].[DimTrack] OFF;

SET IDENTITY_INSERT [dbo].[DimCustomer] ON;
INSERT INTO [dbo].[DimCustomer]
           ([CustomerId],[CustomerCode],[FirstName],[LastName],[Company]
           ,[Address],[City],[State],[Country],[PostalCode],[Phone],[Fax],[Email], [SCDEffectiveFrom])
     VALUES
           (-1, -1, 'Unknown', 'Unknown',  'Unknown', 'Unknown',  'Unknown', 
				'Unknown',  'Unknown', 'Unknown',  'Unknown', 'Unknown',  'Unknown', '1900-01-01')
SET IDENTITY_INSERT [dbo].[DimCustomer] OFF;

exec spCreateDimDate;

